﻿using System;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Text.Json;
using System.Diagnostics;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Reflection;
using System.Data.Common;
using Fast.Framework.Interfaces;
using Fast.Framework.Extensions;
using Fast.Framework.Models;
using Oracle.ManagedDataAccess.Client;
using MySqlConnector;
using Npgsql;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;

namespace Fast.Framework.Test
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                #region 数据库选项
                //var options = new DefaultDbOptions()
                //{
                //    DbType = DbType.SQLServer,
                //    ProviderName = "System.Data.SqlClient",
                //    FactoryTypeAssemblyQualifiedName = "System.Data.SqlClient.SqlClientFactory,System.Data",
                //    ConnectionStrings = "server=localhost;database=Test;user=sa;pwd=123456789;min pool size=3;max pool size=100;connect timeout =30;"
                //};

                var options = new DefaultDbOptions()
                {
                    DbType = DbType.MySQL,
                    ProviderName = "MySqlConnector",
                    FactoryName = "MySqlConnector.MySqlConnectorFactory,MySqlConnector",
                    ConnectionStrings = "server=localhost;database=Test;user=root;pwd=123456789;port=3306;min pool size=3;max pool size=100;connect timeout =30;AllowLoadLocalInfile=True;"
                };

                //var options = new DefaultDbOptions()
                //{
                //    DbType = DbType.PostgreSQL,
                //    ProviderName = "Npgsql",
                //    FactoryName = "Npgsql.NpgsqlFactory,Npgsql",
                //    ConnectionStrings = "host=localhost;database=test;userid=postgres;pwd=123456789;port=5432;"
                //};

                //var options = new DefaultDbOptions()
                //{
                //    DbType = DbType.Oracle,
                //    ProviderName = "Oracle.ManagedDataAccess.Client",
                //    FactoryName = "Oracle.ManagedDataAccess.Client.OracleClientFactory,Oracle.ManagedDataAccess",
                //    ConnectionStrings = "data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)));user id=system;password=Oracle2022;min pool size=3;max pool size=100;connect timeout =30;"
                //};

                //var options = new DefaultDbOptions()
                //{
                //    DbType = DbType.SQLite,
                //    ProviderName = "System.Data.SQLite",
                //    FactoryName = "System.Data.SQLite.SQLiteFactory,System.Data.SQLite",
                //    ConnectionStrings = "data source=mysqlite.db;pooling=true;"
                //};
                #endregion

                var ado = new Ado<DefaultDbOptions>(options);//原生Ado
                var db = new DbContext<DefaultDbOptions>(ado);//数据库上下文

                #region 增 删 改
                //实体对象插入
                {
                    var result = await db.Insert(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体对象插入并返回自增ID 仅支持 SQLServer Mysql Sqlite
                {
                    var result = await db.Insert(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteReturnIdentityAsync();
                    Console.WriteLine(result);
                }

                //实体对象列表插入 列表插入不支持批量返回自增ID
                {
                    var list = new List<ProductModel>();
                    list.Add(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" });
                    list.Add(new ProductModel() { ProductCode = "1002", ProductName = "测试产品2" });
                    var result = await db.Insert(list).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象插入 必须使用 As 方法显示指定表名称
                {
                    var result = db.Insert(new { ProductCode = "1001", ProductName = "测试产品1" }).As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象列表插入 必须使用As 显示指定表名称
                {
                    var list = new List<object>();
                    list.Add(new { ProductCode = "1001", ProductName = "测试产品1" });
                    list.Add(new { ProductCode = "1002", ProductName = "测试产品2" });
                    var result = await db.Insert(list).As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体无条件删除
                {
                    var result = await db.Delete<ProductModel>().ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体条件删除
                {
                    var result = await db.Delete<ProductModel>().Where(w => w.ProductId == 1).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //父类对象删除
                {
                    var result = db.Delete<object>().As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象条件删除
                {
                    var obj = new { ProductCode = "测试" };
                    var result = await db.Delete(obj).Where(w => w.ProductCode == obj.ProductCode).As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体对象无条件更新
                {
                    var result = await db.Update(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体对象条件更新
                {
                    var result = await db.Update(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).Where(w => w.ProductId == 1).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //实体对象列表更新
                {
                    var list = new List<ProductModel>();
                    for (int i = 0; i < 10; i++)
                    {
                        list.Add(new ProductModel() { ProductId = 16395 + i, ProductCode = $"{16395 + i}", ProductName = $"{16395 + i}" });
                    }
                    var result = await db.Update(list).ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象无条件更新
                {
                    var result = await db.Update(new { ProductCode = "1001", ProductName = "测试产品1" }).As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象条件更新
                {
                    var result = await db.Update(new { ProductId = 1, ProductCode = "1001", ProductName = "测试产品1" }).Where(w => w.ProductId == 1).As("Product").ExecuteAsync();
                    Console.WriteLine(result);
                }

                //匿名对象列表更新 需要显示指定主键名称
                {
                    var list = new List<object>();
                    for (int i = 0; i < 10; i++)
                    {
                        list.Add(new { ProductId = 16395 + i, ProductCode = $"{16395 + i}", ProductName = $"{16395 + i}" });
                    }
                    var result = await db.Update(list.ToList()).As("Product").Where("ProductId").ExecuteAsync();
                    Console.WriteLine(result);
                }

                #endregion

                #region 查

                //返回单个对象
                {
                    var data = await db.Query<ProductModel>().Where(w => w.ProductId == 1).FirstAsync();
                }

                //返回列表
                {
                    var data = await db.Query<ProductModel>().ToListAsync();
                }

                //返回字典
                {
                    var data = await db.Query<ProductModel>().DictionaryAsync();
                }

                //返回字典列表
                {
                    var data = await db.Query<ProductModel>().DictionaryListAsync();
                }

                //分页查询
                {
                    var data = await db.Query<ProductModel>().ToPageListAsync(1, 10);
                    Console.WriteLine(JsonSerializer.Serialize(data.Data));//页数据
                    Console.WriteLine($"总数:{data.Count}");
                }

                //联表查询
                {
                    var data = await db.Query<ProductModel>().InnerJoin<ProductCategoryModel>((a, b) => a.CategoryId == b.CategoryId).ToListAsync();
                }

                //分组查询
                {
                    var data = await db.Query<ProductModel>().GroupBy(g => g.ProductName).Select(s => new { Count = 1.Count(), s.ProductName }).ToListAsync();
                }

                //排序查询
                {
                    var data = await db.Query<ProductModel>().OrderBy(o => o.ProductName).ToListAsync();
                }

                //动态条件表达式
                {
                    var obj = new { ProductName = "测试" };
                    var ex = DynamicWhereExpression.Create<ProductModel>().AndIF(!string.IsNullOrWhiteSpace(obj.ProductName), a => a.ProductName == obj.ProductName);

                    var data = await db.Query<ProductModel>().Where(ex.Build()).ToListAsync();
                }

                // 合并查询
                {
                    var query1 = db.Query<ProductModel>().Where(w => w.ProductId < 100);
                    var query2 = db.Query<ProductModel>().Where(w => w.ProductId > 100);
                    var data = await db.UnionAll(query1, query2).ToListAsync();
                }


                #endregion

                #region 批量复制 建议数据量达到500以上使用这个方法 仅支持 SqlServer MySql Oracle PostgreSql
                {
                    var list = new List<ProductModel>();
                    for (int i = 1; i <= 10000; i++)
                    {
                        list.Add(new ProductModel() { ProductCode = i.ToString(), ProductName = $"测试{i}" });
                    }
                    // 因为 ProductModel 类还有其它属性 所以需要使用 Select 扩展方法 显示指定要导入的属性名称和表名称
                    var result = await db.BigData().BulkCopyAsync(list.Select(s => new { s.ProductCode, s.ProductName }), "Product");
                    Console.WriteLine(result);
                }
                #endregion

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }
    }
}